{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 数据规整化方面的话题\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import time\n",
    "from pandas import DataFrame, Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 时间序列以及截面对齐"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            AAPL  MSFT   XOM    IBM     SPX\n",
      "1990-02-01  7.86  0.51  6.12  16.79  328.79\n",
      "1990-02-02  8.00  0.51  6.24  16.89  330.92\n",
      "1990-02-05  8.18  0.51  6.25  17.32  331.85\n",
      "1990-02-06  8.12  0.51  6.23  17.56  329.66\n",
      "1990-02-07  7.77  0.51  6.33  17.93  333.75\n",
      "              AAPL   MSFT    XOM     IBM      SPX\n",
      "2011-10-10  388.81  26.94  76.28  186.62  1194.89\n",
      "2011-10-11  400.29  27.00  76.27  185.00  1195.54\n",
      "2011-10-12  402.19  26.96  77.16  186.12  1207.25\n",
      "2011-10-13  408.43  27.18  76.37  186.82  1203.66\n",
      "2011-10-14  422.00  27.27  78.11  190.53  1224.58\n"
     ]
    }
   ],
   "source": [
    "close_px_all = pd.read_csv('../data/stock_px.csv', parse_dates=True, index_col=0)\n",
    "close_px = close_px_all[['AAPL', 'MSFT', 'XOM', 'IBM', 'SPX']]\n",
    "print(close_px.head())\n",
    "print(close_px.tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AAPL</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>XOM</th>\n",
       "      <th>SPX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-02-01</th>\n",
       "      <td>4193200.0</td>\n",
       "      <td>89193600.0</td>\n",
       "      <td>2916400.0</td>\n",
       "      <td>154580000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-02</th>\n",
       "      <td>4248800.0</td>\n",
       "      <td>71395200.0</td>\n",
       "      <td>4250000.0</td>\n",
       "      <td>164400000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-05</th>\n",
       "      <td>3653200.0</td>\n",
       "      <td>59731200.0</td>\n",
       "      <td>5880800.0</td>\n",
       "      <td>130950000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-06</th>\n",
       "      <td>2640000.0</td>\n",
       "      <td>81964800.0</td>\n",
       "      <td>4750800.0</td>\n",
       "      <td>134070000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-07</th>\n",
       "      <td>11180800.0</td>\n",
       "      <td>134150400.0</td>\n",
       "      <td>4124800.0</td>\n",
       "      <td>186710000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  AAPL         MSFT        XOM          SPX\n",
       "1990-02-01   4193200.0   89193600.0  2916400.0  154580000.0\n",
       "1990-02-02   4248800.0   71395200.0  4250000.0  164400000.0\n",
       "1990-02-05   3653200.0   59731200.0  5880800.0  130950000.0\n",
       "1990-02-06   2640000.0   81964800.0  4750800.0  134070000.0\n",
       "1990-02-07  11180800.0  134150400.0  4124800.0  186710000.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "volume_all = pd.read_csv('../data/volume.csv', parse_dates=True, index_col=0)\n",
    "volume = volume_all[['AAPL', 'MSFT', 'XOM', 'SPX']]\n",
    "volume.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AAPL</th>\n",
       "      <th>IBM</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>SPX</th>\n",
       "      <th>XOM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1990-02-01</th>\n",
       "      <td>32958552.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45488736.0</td>\n",
       "      <td>5.082436e+10</td>\n",
       "      <td>17848368.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-02</th>\n",
       "      <td>33990400.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36411552.0</td>\n",
       "      <td>5.440325e+10</td>\n",
       "      <td>26520000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-05</th>\n",
       "      <td>29883176.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30462912.0</td>\n",
       "      <td>4.345576e+10</td>\n",
       "      <td>36755000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-06</th>\n",
       "      <td>21436800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41802048.0</td>\n",
       "      <td>4.419752e+10</td>\n",
       "      <td>29597484.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990-02-07</th>\n",
       "      <td>86874816.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68416704.0</td>\n",
       "      <td>6.231446e+10</td>\n",
       "      <td>26109984.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  AAPL  IBM        MSFT           SPX         XOM\n",
       "1990-02-01  32958552.0  NaN  45488736.0  5.082436e+10  17848368.0\n",
       "1990-02-02  33990400.0  NaN  36411552.0  5.440325e+10  26520000.0\n",
       "1990-02-05  29883176.0  NaN  30462912.0  4.345576e+10  36755000.0\n",
       "1990-02-06  21436800.0  NaN  41802048.0  4.419752e+10  29597484.0\n",
       "1990-02-07  86874816.0  NaN  68416704.0  6.231446e+10  26109984.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(close_px * volume).head() # 自动匹配对应位置，计算当天成交所额。IBM没有就填充NA。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AAPL      81.246271\n",
       "IBM             NaN\n",
       "MSFT      16.923765\n",
       "SPX     1134.418556\n",
       "XOM       50.520303\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vwap = (close_px * volume).sum() / volume.sum()\n",
    "vwap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AAPL      81.246271\n",
       "MSFT      16.923765\n",
       "SPX     1134.418556\n",
       "XOM       50.520303\n",
       "dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vwap.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            AAPL  MSFT   XOM     SPX\n",
      "1990-02-01  7.86  0.51  6.12  328.79\n",
      "1990-02-02  8.00  0.51  6.24  330.92\n",
      "1990-02-05  8.18  0.51  6.25  331.85\n",
      "1990-02-06  8.12  0.51  6.23  329.66\n",
      "1990-02-07  7.77  0.51  6.33  333.75\n",
      "1990-02-08  7.71  0.51  6.35  332.96\n",
      "1990-02-09  8.00  0.52  6.37  333.62\n",
      "1990-02-12  7.94  0.52  6.22  330.08\n",
      "1990-02-13  8.06  0.52  6.23  331.02\n",
      "1990-02-14  8.00  0.52  6.20  332.01\n",
      "                  AAPL         MSFT        XOM          SPX\n",
      "1990-02-01   4193200.0   89193600.0  2916400.0  154580000.0\n",
      "1990-02-02   4248800.0   71395200.0  4250000.0  164400000.0\n",
      "1990-02-05   3653200.0   59731200.0  5880800.0  130950000.0\n",
      "1990-02-06   2640000.0   81964800.0  4750800.0  134070000.0\n",
      "1990-02-07  11180800.0  134150400.0  4124800.0  186710000.0\n",
      "1990-02-08   6680000.0   95225600.0  5651200.0  176240000.0\n",
      "1990-02-09   6004400.0   62380800.0  3384800.0  146910000.0\n",
      "1990-02-12   2695600.0   56086400.0  2698000.0  118390000.0\n",
      "1990-02-13   3653600.0   58752000.0  3564800.0  144490000.0\n",
      "1990-02-14   3448000.0   35868800.0  2830000.0  138530000.0\n"
     ]
    }
   ],
   "source": [
    "tp = close_px.align(volume, join='inner') # 返回与volume对齐的元组\n",
    "# tp[0]是close_px，tp[1]是volume。\n",
    "print(tp[0][:10])\n",
    "print(tp[1][:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>three</th>\n",
       "      <th>two</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  three  two\n",
       "a  0.0    1.0  NaN\n",
       "b  1.0    NaN  1.0\n",
       "c  2.0    2.0  2.0\n",
       "d  NaN    NaN  0.0\n",
       "e  NaN    NaN  3.0\n",
       "f  NaN    0.0  NaN"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = Series(range(3), index=['a', 'b', 'c'])\n",
    "s2 = Series(range(4), index=['d', 'b', 'c', 'e'])\n",
    "s3 = Series(range(3), index=['f', 'a', 'c'])\n",
    "df = DataFrame({'one': s1, 'two': s2, 'three': s3})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>three</th>\n",
       "      <th>two</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  three  two\n",
       "f  NaN    0.0  NaN\n",
       "a  0.0    1.0  NaN\n",
       "c  2.0    2.0  2.0\n",
       "e  NaN    NaN  3.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = DataFrame({'one': s1, 'two': s2, 'three': s3}, index=list('face')) # 显示定义索引对齐\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 频率不同的时间序列的运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-13    1.015990\n",
       "2012-06-20   -0.482544\n",
       "2012-06-27    0.462347\n",
       "Freq: W-WED, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts1 = Series(np.random.randn(3),\n",
    "             index=pd.date_range('2012-6-13', periods=3, freq='W-WED'))\n",
    "ts1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-13    1.015990\n",
       "2012-06-14    1.015990\n",
       "2012-06-15    1.015990\n",
       "2012-06-18    1.015990\n",
       "2012-06-19    1.015990\n",
       "2012-06-20   -0.482544\n",
       "2012-06-21   -0.482544\n",
       "2012-06-22   -0.482544\n",
       "2012-06-25   -0.482544\n",
       "2012-06-26   -0.482544\n",
       "2012-06-27    0.462347\n",
       "Freq: B, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts1 = ts1.resample('B').ffill() # 重新采样，如果没有ffill就需要用ts2.iteritems()遍历访问\n",
    "ts1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-12   -0.182721\n",
       "2012-06-17   -0.453785\n",
       "2012-06-18   -0.881897\n",
       "2012-06-21    0.962825\n",
       "2012-06-22    1.006743\n",
       "2012-06-29    0.196376\n",
       "dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.DatetimeIndex(['2012-6-12',\n",
    "                          '2012-6-17',\n",
    "                          '2012-6-18',\n",
    "                          '2012-6-21',\n",
    "                          '2012-6-22',\n",
    "                          '2012-6-29'])\n",
    "ts2 = Series(np.random.randn(6), index=dates)\n",
    "ts2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-12         NaN\n",
       "2012-06-17    0.562204\n",
       "2012-06-18    0.134092\n",
       "2012-06-21    0.480281\n",
       "2012-06-22    0.524199\n",
       "2012-06-29    0.658723\n",
       "dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts2 + ts1.reindex(ts2.index, method='ffill') # ts1的索引先和ts2对齐，然后相加。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 使用Period"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1984Q2    1.78\n",
       "1984Q3    1.94\n",
       "1984Q4    2.08\n",
       "1985Q1    2.01\n",
       "1985Q2    2.15\n",
       "1985Q3    2.31\n",
       "1985Q4    2.46\n",
       "Freq: Q-SEP, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdp = Series([1.78, 1.94, 2.08, 2.01, 2.15, 2.31, 2.46],\n",
    "             index=pd.period_range('1984Q2', periods=7, freq='Q-SEP'))\n",
    "gdp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1982    0.025\n",
       "1983    0.045\n",
       "1984    0.037\n",
       "1985    0.040\n",
       "Freq: A-DEC, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "infl = Series([0.025, 0.045, 0.037, 0.04],\n",
    "              index=pd.period_range('1982', periods=4, freq='A-DEC'))\n",
    "infl # 显然和gdp的时间频率不一样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1983Q1    0.025\n",
       "1984Q1    0.045\n",
       "1985Q1    0.037\n",
       "1986Q1    0.040\n",
       "Freq: Q-SEP, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "infl_q = infl.asfreq('Q-SEP', how='end') # 调整季度\n",
    "infl_q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1984Q2    0.045\n",
       "1984Q3    0.045\n",
       "1984Q4    0.045\n",
       "1985Q1    0.037\n",
       "1985Q2    0.037\n",
       "1985Q3    0.037\n",
       "1985Q4    0.037\n",
       "Freq: Q-SEP, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "infl_q.reindex(gdp.index, method='ffill') # 索引匹配并且填充缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 时间和“最当前”数据选取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2012-06-01 09:30:00    0.0\n",
      "2012-06-01 09:31:00    1.0\n",
      "2012-06-01 09:32:00    2.0\n",
      "2012-06-01 09:33:00    3.0\n",
      "2012-06-01 09:34:00    4.0\n",
      "dtype: float64\n",
      "2012-06-06 15:55:00    1555.0\n",
      "2012-06-06 15:56:00    1556.0\n",
      "2012-06-06 15:57:00    1557.0\n",
      "2012-06-06 15:58:00    1558.0\n",
      "2012-06-06 15:59:00    1559.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "rng = pd.date_range('2012-06-01 09:30', '2012-06-01 15:59', freq='T') # 交易时段按分钟采样\n",
    "rng = rng.append([rng + pd.offsets.BDay(i) for i in range(1, 4)]) # 再补4天\n",
    "ts = Series(np.arange(len(rng), dtype=float), index=rng)\n",
    "print(ts.head())\n",
    "print(ts.tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-01 10:00:00      30.0\n",
       "2012-06-04 10:00:00     420.0\n",
       "2012-06-05 10:00:00     810.0\n",
       "2012-06-06 10:00:00    1200.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts[time(10, 0)] # 抽取10点的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-01 10:00:00      30.0\n",
       "2012-06-04 10:00:00     420.0\n",
       "2012-06-05 10:00:00     810.0\n",
       "2012-06-06 10:00:00    1200.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts.at_time(time(10, 0)) # 和上面等价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-01 10:00:00      30.0\n",
       "2012-06-01 10:01:00      31.0\n",
       "2012-06-04 10:00:00     420.0\n",
       "2012-06-04 10:01:00     421.0\n",
       "2012-06-05 10:00:00     810.0\n",
       "2012-06-05 10:01:00     811.0\n",
       "2012-06-06 10:00:00    1200.0\n",
       "2012-06-06 10:01:00    1201.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts.between_time(time(10, 0), time(10, 1)) # 定位到时间段"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-01 09:50:00    20.0\n",
       "2012-06-01 09:51:00     NaN\n",
       "2012-06-01 09:52:00     NaN\n",
       "2012-06-01 09:53:00    23.0\n",
       "2012-06-01 09:54:00     NaN\n",
       "2012-06-01 09:55:00    25.0\n",
       "2012-06-01 09:56:00     NaN\n",
       "2012-06-01 09:57:00     NaN\n",
       "2012-06-01 09:58:00     NaN\n",
       "2012-06-01 09:59:00     NaN\n",
       "2012-06-01 10:00:00    30.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexer = np.sort(np.random.permutation(1560)[700:]) # 根据ts随机选个排列，并选择700条及以后的数据排序。\n",
    "irr_ts = ts.copy()\n",
    "irr_ts[indexer] = np.nan # 根据随机下标索引把一部分时间点数据设置为NA\n",
    "irr_ts['2012-06-01 09:50':'2012-06-01 10:00']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2012-06-01 10:00:00', '2012-06-04 10:00:00',\n",
       "               '2012-06-05 10:00:00', '2012-06-06 10:00:00'],\n",
       "              dtype='datetime64[ns]', freq='B')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selection = pd.date_range('2012-06-01 10:00', periods=4, freq='B') # 连续4个工作日的上午10点\n",
    "selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012-06-01 10:00:00      30.0\n",
       "2012-06-04 10:00:00     420.0\n",
       "2012-06-05 10:00:00     808.0\n",
       "2012-06-06 10:00:00    1200.0\n",
       "Freq: B, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "irr_ts.asof(selection) # 上面多随机几次，确保某个10点数据为NA。asof的话会拿最近数据填充。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 拼接多个数据源"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-12</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-15</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-16</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-17</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-18</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a    b    c\n",
       "2012-06-12  1.0  1.0  1.0\n",
       "2012-06-13  1.0  1.0  1.0\n",
       "2012-06-14  1.0  1.0  1.0\n",
       "2012-06-15  2.0  2.0  2.0\n",
       "2012-06-16  2.0  2.0  2.0\n",
       "2012-06-17  2.0  2.0  2.0\n",
       "2012-06-18  2.0  2.0  2.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1 = DataFrame(np.ones((6, 3), dtype=float),\n",
    "                  columns=['a', 'b', 'c'],\n",
    "                  index=pd.date_range('6/12/2012', periods=6))\n",
    "data2 = DataFrame(np.ones((6, 3), dtype=float) * 2,\n",
    "                  columns=['a', 'b', 'c'],\n",
    "                  index=pd.date_range('6/13/2012', periods=6))\n",
    "spliced = pd.concat([data1.loc[:'2012-06-14'], data2.loc['2012-06-15':]]) # 默认纵向连接\n",
    "spliced"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-12</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-15</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-16</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-17</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-18</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a    b    c    d\n",
       "2012-06-12  1.0  1.0  1.0  NaN\n",
       "2012-06-13  1.0  1.0  1.0  NaN\n",
       "2012-06-14  1.0  1.0  1.0  NaN\n",
       "2012-06-15  2.0  2.0  2.0  2.0\n",
       "2012-06-16  2.0  2.0  2.0  2.0\n",
       "2012-06-17  2.0  2.0  2.0  2.0\n",
       "2012-06-18  2.0  2.0  2.0  2.0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2 = DataFrame(np.ones((6, 4), dtype=float) * 2,\n",
    "                  columns=['a', 'b', 'c', 'd'],\n",
    "                  index=pd.date_range('6/13/2012', periods=6))\n",
    "spliced = pd.concat([data1.loc[:'2012-06-14'], data2.loc['2012-06-15':]])\n",
    "spliced # data1没有d列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-12</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-15</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-16</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-17</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-18</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a    b    c    d\n",
       "2012-06-12  1.0  1.0  1.0  NaN\n",
       "2012-06-13  1.0  1.0  1.0  2.0\n",
       "2012-06-14  1.0  1.0  1.0  2.0\n",
       "2012-06-15  2.0  2.0  2.0  2.0\n",
       "2012-06-16  2.0  2.0  2.0  2.0\n",
       "2012-06-17  2.0  2.0  2.0  2.0\n",
       "2012-06-18  2.0  2.0  2.0  2.0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spliced_filled = spliced.combine_first(data2) # 用data2每一行对应列上的值去填充NA\n",
    "spliced_filled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-12</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-15</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-16</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-17</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-18</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a    b    c    d\n",
       "2012-06-12  1.0  1.0  1.0  NaN\n",
       "2012-06-13  1.0  1.0  1.0  2.0\n",
       "2012-06-14  1.0  1.0  1.0  2.0\n",
       "2012-06-15  2.0  2.0  2.0  2.0\n",
       "2012-06-16  2.0  2.0  2.0  2.0\n",
       "2012-06-17  2.0  2.0  2.0  2.0\n",
       "2012-06-18  2.0  2.0  2.0  2.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spliced.update(data2, overwrite=False)\n",
    "spliced"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-12</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-15</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-16</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-17</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-18</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a    b    c    d\n",
       "2012-06-12  1.0  1.0  1.0  NaN\n",
       "2012-06-13  1.0  1.0  1.0  2.0\n",
       "2012-06-14  1.0  1.0  1.0  2.0\n",
       "2012-06-15  2.0  2.0  2.0  2.0\n",
       "2012-06-16  2.0  2.0  2.0  2.0\n",
       "2012-06-17  2.0  2.0  2.0  2.0\n",
       "2012-06-18  2.0  2.0  2.0  2.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cp_spliced = spliced.copy()\n",
    "cp_spliced"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-12</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-15</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-16</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-17</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-18</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              a    b    c    d\n",
       "2012-06-12  1.0  1.0  1.0  NaN\n",
       "2012-06-13  1.0  1.0  1.0  2.0\n",
       "2012-06-14  1.0  1.0  1.0  2.0\n",
       "2012-06-15  1.0  2.0  1.0  2.0\n",
       "2012-06-16  1.0  2.0  1.0  2.0\n",
       "2012-06-17  1.0  2.0  1.0  2.0\n",
       "2012-06-18  NaN  2.0  NaN  2.0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cp_spliced[['a', 'c']] = data1[['a', 'c']]\n",
    "cp_spliced # data1没有18号的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 收益指数和累计收益"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2011-10-10    388.81\n",
       "2011-10-11    400.29\n",
       "2011-10-12    402.19\n",
       "2011-10-13    408.43\n",
       "2011-10-14    422.00\n",
       "Name: AAPL, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "close_px_all = pd.read_csv('../data/stock_px.csv', parse_dates=True, index_col=0) # 重新读取数据\n",
    "close_px = close_px_all[['AAPL', 'MSFT', 'XOM', 'IBM', 'SPX']]\n",
    "price = close_px['AAPL']\n",
    "price.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.072399874037388123"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price['2011-10-03'] / price['2011-3-01'] - 1 # 计算回报率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>close_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-10-10</th>\n",
       "      <td>388.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-11</th>\n",
       "      <td>400.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-12</th>\n",
       "      <td>402.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-13</th>\n",
       "      <td>408.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-14</th>\n",
       "      <td>422.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            close_price\n",
       "2011-10-10       388.81\n",
       "2011-10-11       400.29\n",
       "2011-10-12       402.19\n",
       "2011-10-13       408.43\n",
       "2011-10-14       422.00"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = DataFrame({'close_price': price})\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1990-02-01         NaN\n",
       "1990-02-02    1.017812\n",
       "1990-02-05    1.022500\n",
       "1990-02-06    0.992665\n",
       "1990-02-07    0.956897\n",
       "Name: close_price, dtype: float64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "returns = df.pct_change()['close_price']\n",
    "(1 + returns).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1990-02-01    1.000000\n",
       "1990-02-02    1.017812\n",
       "1990-02-05    1.040712\n",
       "1990-02-06    1.033079\n",
       "1990-02-07    0.988550\n",
       "Name: close_price, dtype: float64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ret_index = (1 + returns).cumprod()\n",
    "ret_index[0] = 1\n",
    "ret_index.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2011-01-31    43.170483\n",
       "2011-02-28    44.937659\n",
       "2011-03-31    44.339695\n",
       "2011-04-29    44.545802\n",
       "2011-05-31    44.253181\n",
       "2011-06-30    42.706107\n",
       "2011-07-29    49.679389\n",
       "2011-08-31    48.960560\n",
       "2011-09-30    48.513995\n",
       "2011-10-31    53.689567\n",
       "Freq: BM, Name: close_price, dtype: float64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ret_index.resample('BM').last()['2011'] # 2011年每月相对于基准月（1990/2）的收益"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2011-01    1.051959\n",
       "2011-02    1.040935\n",
       "2011-03    0.986693\n",
       "2011-04    1.004648\n",
       "2011-05    0.993431\n",
       "2011-06    0.965040\n",
       "2011-07    1.163285\n",
       "2011-08    0.985531\n",
       "2011-09    0.990879\n",
       "2011-10    1.106682\n",
       "Freq: M, Name: close_price, dtype: float64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m_rets = (1 + returns).resample('M', kind='period').prod() # 每个月的收益率\n",
    "m_rets['2011']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 因为yahoo接口挂了，最后分红的例子就不演示了，原理是一样的。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
